import datetime

import pymysql as sql
from userMgmt.userLink.userRouter import admin

connect = sql.Connect(
    host='rm-bp131vjnd9b99vmuq2o.mysql.rds.aliyuncs.com',
    port=3306,
    user='lin_432718',
    passwd='Lin817818',
    db='rail_traffic',
    charset='utf8'
)
cursor = connect.cursor()


def getAllCopeTest():
    print('return all cope_test')

    sql = "select risk_cope_record.risk_cope_record_id, risk_cope_record.time, risk_cope_record.risk_cope_id, " \
          "risk_cope.risk_id, riskpoint.name, risk_cope_record.operation, risk_cope_record.user_id " \
          "from risk_cope_record, risk_cope, riskpoint where risk_cope_record.risk_cope_id = risk_cope.risk_cope_id " \
          "and risk_cope.risk_id = riskpoint.risk_id " \
          "and risk_cope_record.status = 0"

    print(sql)
    cursor.execute(sql)

    attribute = ['risk_cope_record_id', 'time', 'risk_cope_id', 'risk_id', 'name', 'operation', 'user_id']
    l = []
    for item in cursor.fetchall():
        dic = dict(map(lambda x, y: [x, y], attribute, item))
        dic['time'] = dic['time'].strftime("%Y-%m-%d")
        l.append(dic)

    if l == ():
        print("没有结果")
        return 0
    else:
        print("查询成功")
        return l


def getRiskForTestFromDB():
    print('return all risk_id for test')

    sql = "select risk_cope.risk_cope_id, risk_cope.risk_id, riskpoint.name " \
          "from risk_cope, riskpoint " \
          "where risk_cope.risk_id = riskpoint.risk_id and " \
          "risk_cope.status = 0 and " \
          "risk_cope_id not in ( " \
          "select risk_cope_id " \
          "from risk_cope_record " \
          ")"
    print(sql)
    cursor.execute(sql)

    attribute = ['risk_cope_id', 'risk_id', 'name']
    l = []
    for item in cursor.fetchall():
        dic = dict(map(lambda x, y: [x, y], attribute, item))
        l.append(dic)

    if l == ():
        print("没有结果")
        return 0
    else:
        print("查询成功")
        return l


def getStaffForTestFromDB():
    print('return staff for test')

    sql = "select user_id from user"
    print(sql)
    cursor.execute(sql)

    attribute = ['user_id']
    l = []
    for item in cursor.fetchall():
        dic = dict(map(lambda x, y: [x, y], attribute, item))
        l.append(dic)

    if l == ():
        print("没有结果")
        return 0
    else:
        print("查询成功")
        return l


def addRiskCopeTestToDB(risk_cope_id, operation):
    print('insert a new risk cope test')

    risk_cope_record_id = newRiskCopeRecordID()
    date = datetime.datetime.now().strftime("%Y-%m-%d")
    user_id = str(admin)

    sql = "insert into risk_cope_record values('%s', '%s', '%s', '%s', '%s', '%d')" % (str(risk_cope_record_id), date,
                                                                                 str(risk_cope_id), operation, user_id, 0)
    print(sql)
    try:
        cursor.execute(sql)
        connect.commit()
    except:
        return False
    else:
        return True


def newRiskCopeRecordID():
    sql = "select max(risk_cope_record_id) from risk_cope_record"
    cursor.execute(sql)
    data = cursor.fetchone()
    print(data)
    number = int(data[0]) + 1
    print(number)
    str = "%03d" % number
    print(str)

    return str


def searchCopeTest(word):
    sql = "select risk_cope_record.risk_cope_record_id, risk_cope_record.time, risk_cope_record.risk_cope_id, " \
          "risk_cope.risk_id, riskpoint.name, risk_cope_record.operation, risk_cope_record.user_id " \
          "from risk_cope_record, risk_cope, riskpoint " \
          "where risk_cope_record.risk_cope_id = risk_cope.risk_cope_id " \
          "and risk_cope.risk_id = riskpoint.risk_id " \
          "and risk_cope_record.status = 0 " \
          "and name LIKE '%" + word + "%'"
    print(sql)
    cursor.execute(sql)

    attribute = ['risk_cope_record_id', 'time', 'risk_cope_id', 'risk_id', 'name', 'operation', 'user_id']
    l = []
    for item in cursor.fetchall():
        dic = dict(map(lambda x, y: [x, y], attribute, item))
        dic['time'] = dic['time'].strftime("%Y-%m-%d")
        l.append(dic)

    if l == ():
        print("没有结果")
        return 0
    else:
        print("查询成功")
        return l


def deleteCopeTestFromDB(risk_cope_record_id):
    sql = "Delete FROM risk_cope_record WHERE risk_cope_record_id ='%s'" % str(risk_cope_record_id)
    print(sql)
    try:
        cursor.execute(sql)
        connect.commit()
    except:
        return False
    else:
        return True


def updateStatusInRiskCopeFromDB(status, risk_cope_id):
    sql = "update risk_cope set status = '%d' where risk_cope_id = '%s'" % (status, str(risk_cope_id))
    print(sql)
    try:
        cursor.execute(sql)
        connect.commit()
    except:
        return False
    else:
        return True


def updateStatusInRiskCopeRecordFromDB(risk_cope_record_id):
    sql = "update risk_cope_record set status = '%d' where risk_cope_record_id = '%s'" % (1, str(risk_cope_record_id))
    print(sql)
    try:
        cursor.execute(sql)
        connect.commit()
    except:
        return False
    else:
        return True


def getCopeRecordWithCopeIDFromDB(risk_cope_id):
    sql = "select risk_cope_record_id, time, risk_cope_id, operation, user_id " \
          "from risk_cope_record " \
          "where risk_cope_id = '%s'" % risk_cope_id
    print(sql)
    cursor.execute(sql)

    attribute = ['risk_cope_record_id', 'time', 'risk_cope_id', 'operation', 'user_id']
    l = []
    for item in cursor.fetchall():
        dic = dict(map(lambda x, y: [x, y], attribute, item))
        dic['time'] = dic['time'].strftime("%Y-%m-%d")
        l.append(dic)
    return l
